#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
import numpy as np
from collections import defaultdict
import dianputools
from city_data import get_orderproduct_stats, get_city_flux
from sqlquery import *


def get_product_cat(p):
    dbh = dianputools.connect_to_shangpin()
    query = 'select CateID from t_product where ProductID = "%s";' % (p)
    # print query
    cat = dianputools.get_a_string(dbh, query)

    dbh = dianputools.connect_to_50()
    query = 'select pid from t_category where cateid = "%s";' % (cat)
    pid = dianputools.get_a_string(dbh, query)

    return pid, cat


def get_productid_of_childorder(c):
    dbh = dianputools.connect_to_dingdan()
    query = 'select productid from t_orderproduct where id = "%s";' % (c)
    p = dianputools.get_a_string(dbh, query)
    return p


def get_refund_stats(s, e):
    dbh = dianputools.connect_to_dingdan()

    query = 'select orderid, childorderid, buyerid, refundamount from refundrequest where refundstate=3 and refunddate > "%s" and refunddate < "%s"' % (s, e)
    # print query
    oo = dianputools.query(dbh, query)

    cat2refund = defaultdict(list)
    for o in oo:
        p = get_productid_of_childorder(o[1])
        cat2refund[get_product_cat(p)].append(o)
    # print cat2refund

    ooreturn = {}
    for k, v in cat2refund.items():
        sumo = []
        sumc = []
        sumu = []
        suma = 0.0
        for o, c, u, a in v:
            sumo.append(o)
            sumc.append(c)
            sumu.append(u)
            suma += float(a)
        ooreturn[k] = [len(set(sumo)), len(set(sumc)), len(set(sumu)), suma]

    desc = ['refundorder', 'refundproduct', 'refunduser', 'refundfee']
    return desc, ooreturn


def updaterow(dbh, day, cityids, desc, cdict):
    tablename = 'cat_stats'
    for cid in cdict:
        if cid not in cityids:
            cityids.append(cid)
            dianputools.insertinto(dbh, tablename, ['day', 'cat1', 'cat2'], [day, cid[0], cid[1]])

    for c, v in cdict.items():
        restriction = 'day="%s" and cat1="%s" and cat2="%s"' % (day, c[0], c[1])
        dianputools.update(dbh, tablename, desc, v, restriction)


def get_product_cat_from_order_id(o):
    dbh = dianputools.connect_to_dingdan()
    q = 'select distinct productid from t_orderproduct op left outer join t_order o on o.orderid=op.orderid where o.orderid = "%s";' % (o)
    pids = dianputools.query(dbh, q)
    cat = []
    for p in pids:
        cat.append(get_product_cat(p))

    return cat


def get_comment_stats(s, e):
    dbh = dianputools.connect_to_dianpu()
    q = 'select orderid, type from comment where date >= "%s" and date < "%s"' % (s, e)
    order_and_type = dianputools.query(dbh, q)

    commentcity = defaultdict(list)
    for a, b in order_and_type:
        cat = get_product_cat_from_order_id(a)
        for c in cat:
            commentcity[c].append(b)

    desc = ['comment', 'goodcomment', 'badcomment', 'goodcommentratio', 'badcommentratio']

    scomment = {}
    for k, v in commentcity.items():
        n, g, b = dianputools.filter_total_good_bad_comment(v)
        scomment[k] = [n, g, b, 1.0*g/n, 1.0*b/n]

    return desc, scomment


if __name__ == '__main__':
    if len(sys.argv) != 4:
        # print 'usage: %s year month day' % (sys.argv[0])
        # sys.exit(1)
        year, month, day = 2012, 4, 16
    else:
        year, month, day = map(int, sys.argv[1:])
    print year, month, day

    dtstart, dtend = dianputools.str_day_start_and_end(year, month, day)

    dbh = dianputools.connect_to_50()
    catids = []

    d, orderproduct = get_orderproduct_stats(dtstart, dtend, get_product_cat)
    updaterow(dbh, dtstart, catids, d, orderproduct)

    d, xxflux = get_city_flux(dtstart, dtend, get_product_cat)
    updaterow(dbh, dtstart, catids, d, xxflux)

    d, refund =  get_refund_stats(dtstart, dtend)
    updaterow(dbh, dtstart, catids, d, refund)

    d, comment = get_comment_stats(dtstart, dtend)
    updaterow(dbh, dtstart, catids, d, comment)
    #updaterow(dbh, day, cityids, desc, cdict):
